package com.pf.export;

import com.google.common.collect.Lists;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * excel数据格式转换
 * <p>
 * excel提供的一些数据格式见 {@link BuiltinFormats}
 * <p>
 * 创建数据格式转换对象两种方式：（其实方式1中creationHelper.createDataFormat() 也是调用 workbook.createDataFormat()）
 * 方式1：
 * XSSFCreationHelper creationHelper = workbook.getCreationHelper();
 * creationHelper.createDataFormat().getFormat("#,##0")
 * <p>
 * 方式2：
 * workbook.createDataFormat().getFormat("#,##0")
 *
 * @author PanFei
 * @version 1.0.0
 * @createTime 2023/3/5 18:23
 */
public class DataFormatDemo {

    public static void main(String[] args) throws Exception {
        dataFormatTest();
    }

    /**
     * excel数据格式转换案例
     *
     * @throws Exception
     */
    private static void dataFormatTest() throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("数据转换");

        // 表头
        List<String> headTitles = Lists.newArrayList("数字转换", "日期转换");
        XSSFRow headRow = sheet.createRow(0);
        for (int i = 0; i < headTitles.size(); i++) {
            XSSFCell cell = headRow.createCell(i);
            cell.setCellValue(headTitles.get(i));
        }

        // 数据行
        XSSFRow dataRow = sheet.createRow(1);
        // 数字单元格
        XSSFCell numCell = dataRow.createCell(0);
        numCell.setCellValue(new BigDecimal("10000").doubleValue());
        XSSFCellStyle numCellStyle = workbook.createCellStyle();
        numCellStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
        numCell.setCellStyle(numCellStyle);

        // 日期单元格
        XSSFCell dateCell = dataRow.createCell(1);
        dateCell.setCellValue(new Date());
        XSSFCellStyle dateCellStyle = workbook.createCellStyle();
        dateCellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy/MM/dd"));
        dateCell.setCellStyle(dateCellStyle);

        workbook.write(new FileOutputStream("D:/test/dataFormat.xlsx"));
        System.out.println(">>>> 导出成功...");
    }
}
